<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use PHPExcel;
class Finance extends BaseModel
{
    public $timestamps = false;

    protected $table = 'finance';

    protected $guarded = [];


    public function getOne($where, $fileds = '*')
    {
        return $this->multiSelect($fileds)->multiWhere($where)->first();
    }

    /**
     * 按条件查询全部数据,根据配置显示条数显示
     */
    public function getList(array $where = [], $fields = '*', $order = '', $pageSize = '')
    {
        if ($pageSize) {
            return $this->multiSelect($fields)->multiWhere($where)->multiOrder($order)->paginate($pageSize);
        } else {
            return $this->multiSelect($fields)->multiWhere($where)->multiOrder($order)->get();
        }
    }

    /**
     *插入数据
     */
    public function add($data)
    {
        return $this->_add($data);
    }


    /***
     * @param array $id
     * @param array $data
     * 更新数据
     */
    public function up($where, $data)
    {
        return $this->_updata($where, $data);
    }

    /**
     * @param $id
     * 删除数据
     */
    public function del($where)
    {
        return $this->_del($where);
    }


    /**
     * 表格导出
     */
    public function payExcel($request)
    {
        //时间筛选
        $add_time = $request['add_time'];
        $end_time = $request['end_time'];
        //将英文文本日期时间解析为 Unix 时间戳
        $add_time = empty($add_time) ? 0 : strtotime($add_time);
        $end_time = empty($end_time) ? 0 : strtotime($end_time);
        $where['finance.add_time'] = array('lt', $end_time);
        $list = DB::table('finance')
            ->leftJoin('member', 'finance.member_id', '=', 'member.member_id')
            ->leftJoin('finance_type', 'finance.type', '=', 'finance_type.id')
            ->leftJoin('currency', 'finance.currency_id', '=', 'currency.currency_id')
            ->orderBy('add_time', 'desc')
            ->where('finance.add_time', '<', $end_time)
            ->where('finance.add_time', '>=', $add_time)
            ->select('finance.finance_id', DB::raw('(xnb_member.name) as username,(xnb_finance_type.name) as typename'), 'finance.content', 'finance.money', 'currency.currency_name', 'finance.money_type', 'finance.add_time', 'finance.currency_id')
            ->get();
        foreach ($list as $k => $v) {
            if ($v->money_type == 1) {
                $list[$k]->money_type = '支出';
            } else {
                $list[$k]->money_type = '收入';
            }
            if ($v->currency_id == 0) {
                $list[$k]->currency_name = '人民币';
            }
            $list[$k]->add_time = date('Y-m-d H:i:s', $v->add_time);
        };
        $re = Config::get();
        foreach ($re as $k => $v) {
            $data[$v['key']] = $v['value'];
        }
        $filename = $data['name'] . "财务日志-" . date('Y-m-d', time());
        $excel = new PHPExcel();
        //设置标题
        $excel->getActiveSheet()->setTitle($filename);
        //设置表头
        $excel->getActiveSheet()
            ->setCellValue('A1', '日志编号')
            ->setCellValue('B1', '所属')
            ->setCellValue('C1', '财务类型')
            ->setCellValue('D1', '内容')
            ->setCellValue('E1', '金额')
            ->setCellValue('F1', '币种')
            ->setCellValue('G1', '收入/支出')
            ->setCellValue('H1', '时间');

        //用foreach从第二行开始写数据，因为第一行是表头
        $i = 2;
        foreach ($list as $val) {
            $excel->getActiveSheet()->setCellValue('A' . $i, $val->finance_id)
                ->setCellValue('B' . $i, $val->username)
                ->setCellValue('C' . $i, $val->typename)
                ->setCellValue('D' . $i, $val->content)
                ->setCellValue('E' . $i, $val->money)
                ->setCellValue('F' . $i, $val->currency_name)
                ->setCellValue('G' . $i, $val->money_type)
                ->setCellValue('H' . $i, $val->add_time);
            $i++;
        }
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表  
        $excel->setActiveSheetIndex(0);
        $obj_Writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');

        //设置header
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename . '"');
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        //支持浏览器下载生成的文档 
        $all = $obj_Writer->save('php://output');//输出
        return $all;
    }


    /**
     * 众筹推荐奖励列表
     */
    public function zhongchouAwardsList($request)
    {
        $where['type'] = 12;
        $rows = $this->leftJoin('member','member.member_id','=','finance.member_id')->leftJoin('currency','currency.currency_id','=','finance.currency_id')->select('finance.finance_id','member.email','finance.content','currency.currency_id','currency.currency_name','finance.money','finance.add_time')->multiWhere($where)->orderBy('add_time','desc')->paginate(5);

        if ($rows) {
            $rows = $rows->toArray();
            $rows['pageNoList'] = $this->getPageNoList($rows['last_page'], request('page', 1), 3);
            //dd($rows);
            return $rows;
        }

    }

    /**
     * @return mixed
     * 财务日志
     */
    public function getFinanceLog()
    {
        $where['member_id'] = 87;
        $field = ['finance.*','finance_type.name as typename'];
        $list = $this->multiWhere($where)->leftJoin('finance_type','finance.type','=','finance_type.id')->orderBy('finance_id','desc')->select($field)->paginate(10);

        foreach($list as $k=>$v)
        {
            if($v['currency_id'] == 0){
                $list[$k]['currency_name'] = "人民币";
            }else{
                $cu = Currency::where('currency_id','=',$v['currency_id'])->first();
                $list[$k]['currency_name'] = $cu['currency_name'];
            }
        }

        if ($list) {
            $list = $list->toArray();
            $list['pageNoList'] = $this->getPageNoList($list['last_page'], request('page', 1), 5);
            return $list;
        }


    }

    /**
     * 财务日志数量
     */
    public function getFinanceNum()
    {

        $where['member_id'] = 87;
        $count = $this->multiWhere($where)->leftJoin('finance_type','finance.type','=','finance_type.id')->count();
        return $count;

    }



 }
